/* to get the previous Part Numbers */

--SELECT PART_NUMBER FROM FN_GET_FLOW_BY_FGPARTNUMBER('CY2SSTV857ZXC-27T') WHERE PS_TYPE = 'Tape & Reel' AND PS_TYPE_NAME = 'Class Store' 

Create FUNCTION FN_GET_FLOW_BY_FGPARTNUMBER( @PART_NUMBER VARCHAR(50))
 
 RETURNS  @FN_RESULT TABLE( PROCESS_STEP_ID INT, ps_type varchar(50), ps_type_name varchar(50), part_number varchar(50))

AS
BEGIN	
	DECLARE @PROCESSSTEP_ID		INT
	DECLARE @TEMP_PROCESSSTEP_ID 	INT
	DECLARE @PS_TYPE 		VARCHAR(50)
	DECLARE @PROCESS_TYPE_NAME 	VARCHAR(50)
	DECLARE @PRODUCT_NAME		VARCHAR(50)
	DECLARE @PROCESSSTEPS_CURSOR	CURSOR
	
	SELECT @PROCESSSTEP_ID = CONVERT(VARCHAR, P.PROCESSSTEP_ID) FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND P.PART_NUMBER = @PART_NUMBER AND PS.PS_TYPE_NAME = 'FINISHED GOODS'

	SET @PROCESSSTEPS_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID, PS_TYPE, PROCESS_TYPE_NAME, PRODUCT_NAME FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID)

	OPEN @PROCESSSTEPS_CURSOR
	FETCH NEXT FROM @PROCESSSTEPS_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @PROCESS_TYPE_NAME, @PRODUCT_NAME

	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
	
		IF @PROCESSSTEP_ID > 0
		BEGIN
			INSERT INTO @FN_RESULT VALUES ( @PROCESSSTEP_ID, @PS_TYPE, @PROCESS_TYPE_NAME, @PRODUCT_NAME ) 
		END

		FETCH NEXT FROM @PROCESSSTEPS_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @PROCESS_TYPE_NAME, @PRODUCT_NAME
 	END
	CLOSE @PROCESSSTEPS_CURSOR
	DEALLOCATE @PROCESSSTEPS_CURSOR
RETURN
END
